/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package csse3004.GD.DA;

/**
 *
 * @author Tom McNair - S41758984
 */
import csse3004.GD.MCAnswer;
import csse3004.GD.MCQuestion;
import csse3004.GD.Question;
import csse3004.GD.SRAnswer;
import csse3004.GD.SRQuestion;
import java.util.logging.Logger;

//import java.sql.*;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;

abstract class GenericDA {


    public static final int WEB = 1;
    public static final int MASTER = 2;
    public static final int CREATOR = 3;
    public static final int ADMIN = 4;
    public static final int SYSADMIN = 5;
    private static final Logger m_logger = Logger.getLogger(GenericDA.class.getName());

    //public final ResultSet DatabaseQuery(String query, Connection conn)
    public final ResultSet DatabaseQuery(String query)            
            throws ClassNotFoundException, SQLException {
        m_logger.info(query);
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //String url = "jdbc:oracle:thin:@localhost:1521/XE";
        //Connection conn = DriverManager.getConnection(url,"dbpoll","1234");
        String url = "jdbc:oracle:thin:@oracle.students.itee.uq.edu.au:1521:iteeo";
        Connection conn = DriverManager.getConnection(url, "CSSE3004GD", "695458");
        //conn = DriverManager.getConnection(url, "CSSE3004GD", "695458");

        //conn.setAutoCommit(false);

        try {
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(query);
            return rset;
        } catch (Exception ex) {
            m_logger.info("Database Connection Failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    public final boolean DatabaseInsert(String query)
            throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.driver.OracleDriver");
        //String url = "jdbc:oracle:thin:@localhost:1521/XE";
        //Connection conn = DriverManager.getConnection(url,"dbpoll","1234");

        String url = "jdbc:oracle:thin:@oracle.students.itee.uq.edu.au:1521:iteeo";
        Connection conn = DriverManager.getConnection(url, "CSSE3004GD", "695458");
        //conn = DriverManager.getConnection(url, "CSSE3004GD", "695458");


        conn.setAutoCommit(false);


        try {
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(query);
            conn.commit();
            return true;
        } catch (Exception ex) {
            m_logger.info("Database Insert Failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    public final boolean checkFieldExistsString(String table, String field, String value)
            throws ClassNotFoundException, SQLException {

        String sql = "SELECT * FROM " + table + " WHERE " + field + " = '" + value + "'";

        Class.forName("oracle.jdbc.driver.OracleDriver");
        //String url = "jdbc:oracle:thin:@localhost:1521/XE";
        //Connection conn = DriverManager.getConnection(url,"dbpoll","1234");

        String url = "jdbc:oracle:thin:@oracle.students.itee.uq.edu.au:1521:iteeo";
        Connection conn = DriverManager.getConnection(url, "CSSE3004GD", "695458");


        conn.setAutoCommit(false);


        try {
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(sql);

            if (result.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            m_logger.info("Database Connection Failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    public final boolean checkFieldExistsInt(String table, String field, int value)
            throws ClassNotFoundException, SQLException {

        String sql = "SELECT * FROM " + table + " WHERE " + field + " = " + value;

        Class.forName("oracle.jdbc.driver.OracleDriver");
        //String url = "jdbc:oracle:thin:@localhost:1521/XE";
        // Connection conn = DriverManager.getConnection(url,"dbpoll","1234");

        String url = "jdbc:oracle:thin:@oracle.students.itee.uq.edu.au:1521:iteeo";
        Connection conn = DriverManager.getConnection(url, "CSSE3004GD", "695458");

        conn.setAutoCommit(false);


        try {
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(sql);

            if (result.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            m_logger.info("Database Connection Failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    public final int getNextID(String Table) {
        int idNumber;

        String sql = "Select Count(*) From " + Table;
        m_logger.info(sql);

        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                //m_logger.info("still working");
                idNumber = results.getInt(1);
                idNumber++;
            } else {
                m_logger.info("oh crap");
                idNumber = -2;
            }
        } catch (Exception ex) {
            m_logger.info("getNextID() failed\n");
            m_logger.info(ex.toString());
            return -1;
        }
        return idNumber;
    }

    public final String getUserType(int userID) {
        String sql = "SELECT Type FORM Users WHERE User_ID = " + userID;

        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                return results.getString("Type");
            } else {
                m_logger.info("User doesn't exist");
                return null;
            }

        } catch (Exception ex) {
            m_logger.info("getUserType() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    public boolean hasPermission(int userID, int requiredType) {
        String sql = "SELECT * FROM Users WHERE User_ID = " + userID;
        try {
            ResultSet results = DatabaseQuery(sql);
            String type = results.getString("Type");
            int userType = 0;

            if (type.equals("Web")) {
                userType = 1;
            } else if (type.equals("Master")) {
                userType = 2;
            } else if (type.equals("Creator")) {
                userType = 3;
            } else if (type.equals("Admin")) {
                userType = 4;
            } else if (type.equals("SysAdmin")) {
                userType = 5;
            }

            if (requiredType <= userType) {
                return true;
            } else {
                return false;
            }
        } catch (Exception Ex) {
            m_logger.info("hasPermissions() failed\n");
            m_logger.info(Ex.toString());
            return false;
        }
    }

    public ArrayList<Question> getQuestions(int pollID) {
        String sql = "SELECT * FROM Question WHERE Poll_ID = " + pollID + " AND Deleted = 'No' AND Overwritten = 'No'";
        m_logger.info(sql);

        ArrayList<Question> questionArray = new ArrayList<Question>();
        Question question;

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                if (results.getString("Question_Type").equals("MC")) {
                    question = new MCQuestion(); //TODO TOM fix this
                    question = getMCQuestion(results.getInt("Question_ID"));
                } else {
                    question = new SRQuestion();
                    question = getSRQuestion(results.getInt("Question_ID"));
                }
                m_logger.info("I'm back, and i'm bad");
                question.setId(results.getInt("Question_ID"));
                m_logger.info("Question ID");
                question.setText(results.getString("Question_Text"));
                m_logger.info("Question Text");
                question.setStatus(results.getInt("Status"));
                m_logger.info("Question Status");
                question.setPollId(pollID);
                m_logger.info("Poll ID");

                questionArray.add(question);
                m_logger.info("Pyrovilia, lost moon of poosh, sorted.");
            }

            return questionArray;

        } catch (Exception ex) {
            m_logger.info("getQuestions() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    public MCQuestion getMCQuestion(int questionID) {
        //String sql1 = "SELECT * FROM Question WHERE Question_ID = " + questionID;
        String sql2 = "SELECT * FROM MC_Question WHERE Question_ID = " + questionID;
        MCQuestion question = new MCQuestion();

        try {
//            ResultSet results = DatabaseQuery(sql1);
//
//            results.next();
//            question.setId(questionID);
//            question.setText(results.getString("Question_Text"));
//            if (results.getInt("Status") == 0) {
//                m_logger.info("A");
//                question.setPending();
//            } else if (results.getInt("Status") == 1) {
//                m_logger.info("B");
//                question.setOpen();
//            } else {
//                m_logger.info("C");
//                question.setClosed();
//            }
            m_logger.info("hey nonny nonny");
            ResultSet results = DatabaseQuery(sql2);
            m_logger.info("this database is indeed query");
            results.next();

            question.setStyle(results.getString("Style"));
            m_logger.info("tibet");
            if (results.getString("Template").equals("Yes")) {
                question.setTemplate(true);
            } else {
                question.setTemplate(false);
            }
            m_logger.info("ACDC");
            if (results.getString("Ranking").equals("Yes")) {
                question.setRanking(true);
            } else {
                question.setRanking(false);
            }
            m_logger.info("Googel");
            if (results.getString("Demographic").equals("Yes")) {
                question.setDemographic("true");
            } else {
                question.setDemographic("false");
            }
            m_logger.info("Bob");
            if (results.getString("Comparison").equals("Yes")) {
                question.setComparison(1);
            } else {
                question.setComparison(0);
            }
            m_logger.info("Alice's adventures through the windshield glass");
        } catch (Exception ex) {
            m_logger.info("getMCQuestion() failed\n");
            m_logger.info(ex.toString());
        }
        return question;
    }

    public SRQuestion getSRQuestion(int questionID) {
        String sql1 = "SELECT * FROM Question WHERE Question_ID = " + questionID;
        String sql2 = "SELECT * FROM SR_Question WHERE Question_ID = " + questionID;
        SRQuestion question = new SRQuestion();

        try {
//            ResultSet results = DatabaseQuery(sql1);
//            results.next();
//
//            question.setId(questionID);
//            question.setText(results.getString("Question_Text"));
//            if (results.getInt("Status") == 0) {
//                question.setPending();
//            } else if (results.getInt("Status") == 1) {
//                question.setOpen();
//            } else {
//                question.setClosed();
//            }

            ResultSet results = DatabaseQuery(sql2);
            results.next();

            if (results.getString("Question_Type").equals("Numeric")) {
                question.setNumeric();
            } else {
                question.setAlphanumeric();
            }
        } catch (Exception ex) {
            m_logger.info("getSRQuestion() failed\n");
            m_logger.info(ex.toString());
        }
        return question;
    }

    public ArrayList<MCAnswer> getMCAnswers(int questionID) {
        String sql = "SELECT * FROM MC_Answer WHERE Question_ID = " + questionID;

        ArrayList<MCAnswer> answerArray = new ArrayList<MCAnswer>();
        MCAnswer answer;

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                answer = new MCAnswer();

                answer.setId(results.getInt("MC_Answer_ID"));
                answer.setText(results.getString("Answer_Text"));
                answer.setWeighting(results.getInt("Weighting"));

                if (results.getString("Correct").equals("Yes")) {
                    answer.setCorrect(true);
                } else {
                    answer.setCorrect(false);
                }
                answerArray.add(answer);
            }

            return answerArray;

        } catch (Exception ex) {
            m_logger.info("getMCAnswers() failed\n");
            m_logger.info(ex.toString());
        }
        return null;
    }

    public ArrayList<SRAnswer> getSRKeywords(int questionID) {
        String sql = "SELECT * FROM SR_Keyword WHERE Question_ID = " + questionID;

        ArrayList<SRAnswer> keywordArray = new ArrayList<SRAnswer>();
        SRAnswer keyword;

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                keyword = new SRAnswer();

                keyword.setId(results.getInt("SR_Keyword_ID"));
                keyword.setText(results.getString("Keyword"));

                keywordArray.add(keyword);
            }

            return keywordArray;

        } catch (Exception ex) {
            m_logger.info("getSRKeywords() failed\n");
            m_logger.info(ex.toString());
        }
        return null;
    }

    public final Question getQuestion(int questionID) {
        String sql = "SELECT * FROM Question WHERE Question_ID = " + questionID;


        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                Question question;
                if (results.getString("Question_Type").equals("MC")) {
                    question = new MCQuestion(); //TODO TOM fix this
                    question = getMCQuestion(results.getInt("Question_ID"));
                } else {
                    question = new SRQuestion();
                    question = getSRQuestion(results.getInt("Question_ID"));
                }
                question.setText(results.getString("Question_Text")); //TODO @TOM unfuck this shit.
                question.setId(questionID);
                question.setStatus(results.getInt("STATUS"));
                question.setPollId(results.getInt("POLL_ID"));
                return question;
            }
        } catch (Exception ex) {
            m_logger.info("getQuestion() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
        return null;
    }

    public final boolean changePassword(int username, String password) {
        String sql = "UPDATE users SET Password = " + password + " WHERE username = " + username;

        try {
            DatabaseInsert(sql);
            return true;
        } catch (Exception ex) {
            m_logger.info("changePassword() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    public boolean addLocationToPoll(int pollID, double latitude, double longitude) {

        int locationID;

        try {
            ResultSet results = DatabaseQuery("SELECT Location_ID FROM Poll_Location WHERE Poll_ID = "+pollID);
            
            locationID = getNextID("Location");
            String sql2 = "INSERT INTO Location VALUES(" + locationID + ", 'Alderaan', " + latitude + ", " + longitude + ")";
            m_logger.info("Hello mummy " + locationID + "\n");
            if (!DatabaseInsert(sql2)) {
                m_logger.info("Insert into Location table failed\n");
                return false;
            }
            m_logger.info("How the hell am i still in this crapshack\n");
            for (int i = 0; i < 1000000000; i++) {
                int p = 83;
            }
            m_logger.info("Thank god thats over with");
            String sql3;
            if (results.next()) {
                sql3 = "UPDATE Poll_Location SET Location_ID = "+locationID+" WHERE Poll_ID = "+pollID;
                m_logger.info("Location already exists for poll, updating");
            } else {
                sql3 = "INSERT INTO Poll_Location VALUES(" + pollID + ", " + locationID + ")";
                m_logger.info("No existing location found for poll, adding new location");
            }
            if (!DatabaseInsert(sql3)) {
                m_logger.info("Insert into Poll_Location table failed\n");
                return false;
            }
            m_logger.info("Oh thank god\n");
        } catch (Exception ex) {
            m_logger.info("addLocationToPoll() failed\n");
            m_logger.info(ex.toString());
            return false;
        }

        return true;
    }

    public final boolean deactivateAccount(int userID) {
        String sql = "UPDATE Users SET Enabled = 'No' WHERE User_ID = " + userID;

        try {
            if (DatabaseInsert(sql)) {
                return true;
            } else {
                return false;
            }
        } catch (Exception Ex) {
            m_logger.info("deactivateAccount() failed");
            m_logger.info(Ex.toString());
            return false;
        }
    }

    public final boolean activateAccount(int userID) {
        String sql = "UPDATE Users SET Enabled = 'Yes' WHERE User_ID = " + userID;

        try {
            if (DatabaseInsert(sql)) {
                return true;
            } else {
                return false;
            }
        } catch (Exception Ex) {
            m_logger.info("deactivateAccount() failed");
            m_logger.info(Ex.toString());
            return false;
        }
    }

    /**
     * Sets the users password, no validation beyond that the user exists is 
     * required
     * @param userID
     * @param password The password to be set
     * @return false only if the user doesn't exist
     */
    public boolean setPassword(int userID, String password) {
        String sql = "UPDATE Users SET password = '" + password + "' WHERE USER_ID = " + userID;
        m_logger.info(sql);
        try {
            if (DatabaseInsert(sql)) {
                return true;
            } else {
                m_logger.info("User does not exist");
                return false;
            }
        } catch (Exception Ex) {
            m_logger.info("setPassword() failed");
            m_logger.info(Ex.toString());
            return false;
        }
    }

    public String getQuestionType(int questionID) {
        String sql = "SELECT Question_Type FROM Question WHERE Question_ID = " + questionID;
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                String type = results.getString("Question_Type");
                return type;
            }
            return null;

        } catch (Exception Ex) {
            m_logger.info("getQuestionType(" + questionID + ") failed\n");
            m_logger.info(Ex.toString());
            return null;
        }
    }

    public int getQuestionStatus(int questionID) {
        String sql = "SELECT Status FROM Question WHERE Question_ID = " + questionID;
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                return results.getInt("Status");
            }
            return -1;
        } catch (Exception Ex) {
            m_logger.info("getQuestionType(" + questionID + ") failed\n");
            m_logger.info(Ex.toString());
            return -2;
        }
    }

    public boolean previouslyAnswered(int userID, int questionID, String questionType) {
        if (questionType.equals("MC")) {
            String sql = "SELECT * FROM MC_Response WHERE Question_ID = " + questionID + " and User_ID = " + userID;
            try {
                ResultSet results = DatabaseQuery(sql);
                if (results.next()) {
                    int ID = getNextID("MC_Response");
                    sql = "UPDATE MC_Response SET User_ID = userID, ";
                    return true;
                }
                return false;
            } catch (Exception Ex) {
                m_logger.info("previouslyAnswered(" + userID + "," + questionID + "," + questionType + ") failed\n");
                m_logger.info(Ex.toString());
                return false;
            }
        } else {
            return false;
        }
    }

    public final int getActiveQuestionID(int pollID) {
        String sql = "SELECT Active_Question FROM Poll WHERE Poll_ID = " + pollID;
        m_logger.info(sql);
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                int currentID = results.getInt("Active_Question");
                if (currentID == -1) {
                    m_logger.info("No Active Question");
                    return -1;
                }
                return currentID;
            }
            m_logger.info("some error in result.next()");//TODO TOM David added this if results.next code and is unsure what he is doing, check!
            return -1;

        } catch (Exception ex) {
            m_logger.info("getActiveQuestionID() failed\n");
            m_logger.info(ex.toString());
            return -1;
        }
    }

    public final Question getActiveQuestion(int pollID) {
        String sql = "SELECT Active_Question FROM Poll WHERE Poll_ID = " + pollID;
        m_logger.info(sql);
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                int currentID = results.getInt("Active_Question");
                m_logger.info("cur ID: " + currentID);
                if (currentID == -1) {
                    m_logger.info("No Active Question");
                    return null;
                } else {
                    m_logger.info("getQuesiton");
                    return getQuestion(currentID);
                }
            } else {
                return null;
            }
        } catch (Exception ex) {
            m_logger.info("getActiveQuestion() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    /**
     * Will set the next/previous question in the database.
     *
     *@param isNext If the next question (true) or the previous question (false) should be set
     *@param  pollid the id of the poll to change
     *@return the new QuestionID or -1 on error
     **/
    public int setNext(boolean isNext, int pid) {
        int activeQuestion = getActiveQuestionID(pid);
        m_logger.info("Active Question: " + activeQuestion);

        String sql = "SELECT * FROM Question WHERE Poll_ID = " + pid;
        m_logger.info(sql);
        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                if (results.getInt("Question_ID") == activeQuestion) {
                    if (isNext) {
                        if (results.next()) {
                            int newQuestionID = results.getInt("Question_ID");
                            DatabaseInsert("UPDATE Poll SET Active_Question = " + newQuestionID + " WHERE Poll_ID =" + pid);

                            return newQuestionID;
                        }
                        return -1;
                    } else {
                        if (results.previous()) {
                            int newQuestionID = results.getInt("Question_ID");
                            DatabaseInsert("UPDATE Poll SET Active_Question = " + newQuestionID + " WHERE Poll_ID =" + pid);
                            return newQuestionID;
                        }
                        return -1;
                    }
                }
            }
        } catch (Exception ex) {
            m_logger.info("getNextQuestion() failed\n");
            m_logger.info(ex.toString());
            return -1;
        }
        return 1;
    }

    public final String getUserEmail(int userID) {
        String sql = "SELECT Email FROM Users WHERE User_ID = " + userID;
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                String type = results.getString("Email");
                return type;
            }
            return null;

        } catch (Exception Ex) {
            m_logger.info("getUserEmail(" + userID + ") failed\n");
            m_logger.info(Ex.toString());
            return null;
        }
    }

    public final boolean editUserEmail(int userID, String email) {
        String sql = "UPDATE Users SET Email = '" + email + "' WHERE User_ID = " + userID;
        try {
            m_logger.info("In try");
            DatabaseInsert(sql);
            m_logger.info("Inserting...");
            return true;
        } catch (Exception Ex) {
            m_logger.info("editUserEmail() failed\n");
            m_logger.info(Ex.toString());
            return false;
        }
    }

    public final String getUserExpiry(int userID) {
        String sql = "SELECT Expiry_Date FROM User_Expiry WHERE User_ID = " + userID;
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                Long time = results.getLong("Expiry_Date");
                
                Calendar date = new java.util.GregorianCalendar();
                date.setTimeInMillis(time);
                
                DateFormat dataformat =  DateFormat. getDateInstance(DateFormat.LONG);
                                
                return dataformat.format(date.getTime());
            }
            return null;

        } catch (Exception Ex) {
            m_logger.info("getUserExpiry(" + userID + ") failed\n");
            m_logger.info(Ex.toString());
            return null;
        }
    }

    public final boolean editUserExpiry(int userID, String expiry) {
        DateFormat format = DateFormat.getDateTimeInstance(DateFormat.MEDIUM, DateFormat.SHORT);

        java.util.Date date;

        try {
            date = format.parse(expiry);
        } catch (ParseException Ex) {
            m_logger.info("Parse date failed!\n");
            m_logger.info(Ex.toString());
            return false;
        }

        long time = date.getTime();

        String sql = "UPDATE User_Expiry SET Expiry_Date = " + time + " WHERE User_ID = " + userID;
        try {
            if (DatabaseInsert(sql)) {
                return true;
            } else {
                return false;
            }
        } catch (Exception Ex) {
            m_logger.info("editUserExpiry(" + userID + ", " + expiry + ") failed\n");
            m_logger.info(Ex.toString());
            return false;
        }
    }
}
